-- Author: Norm Olsen, modified: Rowan Haigh
DECLARE @doors FLOAT, @speed FLOAT
--SET @surveyid = @surveyidval
SET @doors = @doorsval
SET @speed = @speedval -- (m/min)

SET NOCOUNT ON

SELECT 
  FEG.GROUPING_CODE AS h,
  MAX(AREA_KM2) AS A,
  CAST(COUNT(FEG.GROUPING_CODE) AS FLOAT) AS Nh
  INTO #NSETS
  FROM TRIP_SURVEY S
    INNER JOIN FISHING_EVENT FE ON
    S.TRIP_ID = FE.TRIP_ID 
    INNER JOIN SURVEY_GROUPING SG ON
    S.SURVEY_ID = SG.SURVEY_ID 
    INNER JOIN FISHING_EVENT_GROUPING FEG ON
    SG.GROUPING_CODE = FEG.GROUPING_CODE AND
    FEG.FISHING_EVENT_ID = FE.FISHING_EVENT_ID
    INNER JOIN TRAWL_SPECS TS ON
    FE.FISHING_EVENT_ID = TS.FISHING_EVENT_ID
    INNER JOIN [GROUPING] G ON
    SG.GROUPING_CODE = G.GROUPING_CODE
  WHERE 
    S.SURVEY_ID IN (@surveyid) AND
    ISNULL(TS.USABILITY_CODE,1) = 1
  GROUP BY 
    FEG.GROUPING_CODE

DECLARE @nspp AS FLOAT
SET @nspp = (SELECT COUNT(*)
  FROM PacHarvest.dbo.C_Species S 
  WHERE S.SPECIES_CDE IN (@sppcode) ) 

SELECT 
  FEG.GROUPING_CODE AS h,
  AVG(CATCH_WEIGHT / CASE 
    WHEN FE_BEGIN_BOTTOM_CONTACT_TIME IS NULL THEN
      (DATEDIFF(N, FE_END_DEPLOYMENT_TIME, FE_BEGIN_RETRIEVAL_TIME) * @speed * @doors)
    ELSE
      (DATEDIFF(N, FE_BEGIN_BOTTOM_CONTACT_TIME,
      FE_END_BOTTOM_CONTACT_TIME) * @speed * @doors) END) * 1000 AS mu,
  STDEV(CATCH_WEIGHT / CASE 
    WHEN FE_BEGIN_BOTTOM_CONTACT_TIME IS NULL THEN
      (DATEDIFF(N, FE_END_DEPLOYMENT_TIME, FE_BEGIN_RETRIEVAL_TIME) * @speed * @doors)
    ELSE
      (DATEDIFF(N, FE_BEGIN_BOTTOM_CONTACT_TIME,
      FE_END_BOTTOM_CONTACT_TIME) * @speed * @doors) END) * 1000 AS sd,
  CAST(COUNT(FEG.GROUPING_CODE) AS FLOAT)/@nspp AS nh,
  'nspp' = @nspp
  INTO #MU
  FROM TRIP_SURVEY S
    INNER JOIN FISHING_EVENT FE ON
    S.TRIP_ID = FE.TRIP_ID
    INNER JOIN SURVEY_GROUPING SG ON
    S.SURVEY_ID = SG.SURVEY_ID
    INNER JOIN FISHING_EVENT_GROUPING FEG ON
    SG.GROUPING_CODE = FEG.GROUPING_CODE AND
    FEG.FISHING_EVENT_ID = FE.FISHING_EVENT_ID
    INNER JOIN TRAWL_SPECS TS ON
    FE.FISHING_EVENT_ID = TS.FISHING_EVENT_ID
    INNER JOIN [GROUPING] G ON
    SG.GROUPING_CODE = G.GROUPING_CODE
    INNER JOIN FISHING_EVENT_CATCH FEC ON
    TS.FISHING_EVENT_ID = FEC.FISHING_EVENT_ID
    INNER JOIN CATCH C ON
    FEC.CATCH_ID = C.CATCH_ID
  WHERE 
    S.SURVEY_ID IN (@surveyid) AND 
    ISNULL(TS.USABILITY_CODE,1) = 1 AND
    SPECIES_CODE IN (@sppcode) AND 
    CATCH_WEIGHT IS NOT NULL
  GROUP BY 
    FEG.GROUPING_CODE

SELECT 
  A.h,
  1 - ISNULL(B.nh / A.Nh, 0) AS p,
  B.mu,
  CASE B.nh 
    WHEN 1 THEN SQRT(1/A.Nh)
    ELSE B.sd / B.mu
    END AS rho,
  CASE B.nh  
    WHEN 1 THEN A.Nh
    ELSE 1 / NULLIF(POWER((B.sd / B.mu),2),0)
    END AS nu,
  A.A,
  A.Nh,
  B.nh
  FROM #NSETS A
    LEFT OUTER JOIN #MU B ON A.h = B.h
  ORDER BY A.h

-- getData("gfb_pmr.sql","GFBioSQL",strSpp="396",surveyid=3)
-- getData("gfb_pmr.sql","GFBioSQL",strSpp=c("394","396","440"),surveyid=3)

